﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;
using KSSModel;
namespace KSSOnline.Controller
{
    public class ExportExcel
    {
        public static string ExportByMonth(int month, string location)
        {
            using (var dc = new KSSDBDataContext())
            {
                try
                {
                    var groupDoc = from d in dc.tblDocs
                                   group d by d.createdBy into g
                                   select new
                                              {
                                                  empID = g.First().createdBy,
                                                  docCount = g.Count(),
                                              };
                    var groupDocMonth = from d in dc.tblDocs
                                        where d.createdDate.Year == DateTime.Today.Year && d.createdDate.Month == month
                                        group d by d.createdBy into g
                                        select new
                                        {
                                            empID = g.First().createdBy,
                                            docCount = g.Count(),
                                        };
                    var groupAll = from ds in groupDoc
                                   join d in dc.tblEDMSHRs on ds.empID equals d.mstaffno
                                   select new
                                              {
                                                  d.mstaffno,
                                                  d.mfac,
                                                  d.depnameTh,
                                                  d.sacnameTh,
                                                  d.mnameTh,
                                                  d.surnameTh,
                                                  ds.docCount,

                                              };
                    var result = from g in groupAll
                                 join m in groupDocMonth.DefaultIfEmpty()
                                     on g.mstaffno equals m.empID into k
                                 select new
                                            {
                                                g.mfac,
                                                g.depnameTh,
                                                g.docCount,
                                                g.mnameTh,
                                                g.sacnameTh,
                                                g.surnameTh,
                                                monthlyCount = k.First().docCount
                                            };
                    var serializer = new JavaScriptSerializer();
                    return serializer.Serialize(result);

                }
                catch (Exception ex)
                {

                }
            }
            return "";
        }

        public static void GetReportMoneyRateGroup(int month, SearchCiteria searchCiteria, out List<cashGroupSB> allCashGroup, out List<cashGroupSB> thisMonthCashGroup)
        {
            using (var dc = new KSSDBDataContext())
            {
                var cash = from cashs in dc.tblDocs
                           join hr in dc.tblEDMSHRs on cashs.createdBy equals hr.mstaffno
                           where hr.mlocation.Equals(searchCiteria.Location)
                           select cashs;
                var cashMonth = from cashmonth in cash
                                join hr in dc.tblEDMSHRs on cashmonth.createdBy equals hr.mstaffno
                                where cashmonth.createdDate.Month == month && hr.mlocation.Equals(searchCiteria.Location)
                                select cashmonth;
                allCashGroup = (from cashGroup in cash
                                group cashGroup by cashGroup.cash into g
                                select new cashGroupSB
                                {
                                    price = g.Key ?? 0,
                                    count = g.Count()
                                }).ToList();
                thisMonthCashGroup = (from cashGroupMonth in cashMonth
                                      group cashGroupMonth by cashGroupMonth.cash into g
                                      select new cashGroupSB
                                      {
                                          price = g.Key ?? 0,
                                          count = g.Count()
                                      }).ToList();

            }

        }
        public static List<cateGroupSB> GetReportAllCateGroup(int month, SearchCiteria searchCiteria)
        {
            var allItem = A1ItemHelper.GetItemWithCate();
            using (var dc = new KSSDBDataContext())
            {
                List<cateGroupSB> allCateReport = new List<cateGroupSB>();
                foreach (var item in allItem)
                {
                    for (int i = 0; i < item.Item.Count; i++)
                    {
                        var dAll = (from ds in dc.tblDocs
                                    join hr in dc.tblEDMSHRs on ds.createdBy equals hr.mstaffno
                                    join dsp in dc.docPropertyDatas on ds.id equals dsp.docId
                                    where hr.mlocation.Equals(searchCiteria.Location) && dsp.propertyName == item.Item[i].a1ItemId.ToString() && dsp.dataNvarchar.ToLower() == "true"
                                    select dsp).Count();
                        var dAllMonth = (from ds in dc.tblDocs
                                         join hr in dc.tblEDMSHRs on ds.createdBy equals hr.mstaffno
                                         join dsp in dc.docPropertyDatas on ds.id equals dsp.docId
                                         where hr.mlocation.Equals(searchCiteria.Location) && ds.createdDate.Month == month && dsp.propertyName == item.Item[i].a1ItemId.ToString() && dsp.dataNvarchar.ToLower() == "true"
                                         select dsp).Count();
                        allCateReport.Add(new cateGroupSB { CateName = item.CateName, CateItem = item.Item[i].a1ItemName, count = dAll, isMonthly = false });
                        allCateReport.Add(new cateGroupSB { CateName = item.CateName, CateItem = item.Item[i].a1ItemName, count = dAllMonth, isMonthly = true });
                    }

                }
                return allCateReport;


            }
        }
        public static List<cateGroupSB> GetReportAllCateUserGroup(int month, List<cateGroupSB> properties, SearchCiteria searchCiteria)
        {
            using (var dc = new KSSDBDataContext())
            {
                List<cateGroupSB> allCateReport = new List<cateGroupSB>();
                for (int i = 0; i < properties.Count; i++)
                {
                    var dAll = (from ds in dc.tblDocs
                                join hr in dc.tblEDMSHRs on ds.createdBy equals hr.mstaffno
                                join dsp in dc.docPropertyDatas on ds.id equals dsp.docId
                                where hr.mlocation.Equals(searchCiteria.Location) && dsp.propertyName == properties[i].CateItemId.ToString() && dsp.dataNvarchar.ToLower() == "true"
                                select dsp).Count();
                    var dAllMonth = (from ds in dc.tblDocs
                                     join hr in dc.tblEDMSHRs on ds.createdBy equals hr.mstaffno
                                     join dsp in dc.docPropertyDatas on ds.id equals dsp.docId
                                     where hr.mlocation.Equals(searchCiteria.Location) && ds.createdDate.Month == month && dsp.propertyName == properties[i].CateItemId.ToString() && dsp.dataNvarchar.ToLower() == "true"
                                     select dsp).Count();
                    allCateReport.Add(new cateGroupSB { CateName = properties[i].CateName, CateItem = properties[i].CateItem, count = dAll, isMonthly = false });
                    allCateReport.Add(new cateGroupSB { CateName = properties[i].CateName, CateItem = properties[i].CateItem, count = dAllMonth, isMonthly = true });
                }
                return allCateReport;


            }
        }

        public static void MonthlyTarget(List<kssMonthlyTarget> targets)
        {
            using (var dc = new KSSDBDataContext())
            {
                dc.kssMonthlyTargets.InsertAllOnSubmit(targets);
                dc.SubmitChanges();
            }
        }

        public static void InsertMonthlyTarget(List<kssMonthlyTarget> targets)
        {
            using (var dc = new KSSDBDataContext())
            {

                foreach (var target in targets)
                {
                    var exits = (from currentTarget in dc.kssMonthlyTargets
                                where currentTarget.SectionName == target.SectionName && currentTarget.Month == target.Month && currentTarget.Year == target.Year
                                select currentTarget).FirstOrDefault();
                    if (exits != null)
                    {
                        exits.Target = target.Target;
                        dc.SubmitChanges();
                    }
                    else
                    {
                        dc.kssMonthlyTargets.InsertOnSubmit(target);
                        dc.SubmitChanges();
                    }
                }   
                
                
            }
        }
    }
}
